package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.IGroupReserveDao;
import com.eling.elcms.travel.model.GroupReserve;
import com.eling.elcms.travel.model.RoomStatus;
import com.eling.elcms.travel.model.view.RoomView;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class GroupReserveDaoHibernate extends GenericDaoHibernate<GroupReserve, Long> implements IGroupReserveDao {

	public GroupReserveDaoHibernate() {
		super(GroupReserve.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Long> queryRoomStatus(Long pkOrgBuilding, Long pkRoomType, Date startDate, Date endDate, boolean b) {
		
		SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT		");
		sql.append(" 	trg.pkRoomRegister	");
		sql.append("   FROM	travel_roomregister trg	");
		sql.append("   LEFT JOIN bd_room br ON br.pkRoom = trg.pkRoom	");
		sql.append("   LEFT JOIN travel_roomtype trt ON trt.pkRoomType = br.pkRoomType	");
		sql.append("   LEFT JOIN bd_building bd ON bd.pkOrgBuilding = br.pkOrgBuilding	");
		sql.append("   LEFT JOIN travel_group_roomregister tgrr ON tgrr.pkRoomRegister = trg.pkRoomRegister	");
		sql.append("   LEFT JOIN travel_groupreserve tgr ON tgr.pkGroupReserve = tgrr.pkGroupReserve	");
		sql.append("   LEFT JOIN travel_checkin tc ON tc.pkRoomRegister = trg.pkRoomRegister	");
		sql.append("   WHERE trt.pkRoomType =:pkRoomType	");
		sql.append("   AND bd.pkOrgBuilding =:pkOrgBuilding	");
		if (b) {
			sql.append("   AND tc.checkInDate <= '" + sdf.format(endDate) + "'		");
			sql.append("   AND tc.checkOutDate >= '" + sdf.format(startDate) + "'		");
		} else {
			sql.append("   AND tgr.checkInDate <= '" + sdf.format(endDate) + "'		");
			sql.append("   AND tgr.checkOutDate >= '" + sdf.format(startDate) + "'		");
		}
		sql.append("   GROUP BY trg.pkRoomRegister;		");

		List<Object> objList = getSession().createNativeQuery(sql.toString()).setParameter("pkRoomType", pkRoomType)
				.setParameter("pkOrgBuilding", pkOrgBuilding).list();
		List<Long> pkList = new ArrayList<Long>();
		if(!objList.isEmpty()){
			for(int i = 0; i < objList.size(); i++){
				Long pk = objList.get(i) == null ? null : Long.valueOf(objList.get(i).toString());
				pkList.add(pk);
			}
		}
		
		return pkList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RoomView> queryRoom(Long pkOrgBuilding, Long pkRoomType) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT		");
		sql.append(" 	trg.pkRoomRegister,br.pkRoom,br.code		");
		sql.append("   FROM  travel_roomregister trg		");
		sql.append("   LEFT JOIN bd_room br ON br.pkRoom = trg.pkRoom		");
		sql.append("   LEFT JOIN travel_roomtype trt ON trt.pkRoomType = br.pkRoomType		");
		sql.append("   LEFT JOIN bd_building bd ON bd.pkOrgBuilding = br.pkOrgBuilding		");
		sql.append("   WHERE trt.pkRoomType =:pkRoomType		");
		sql.append("   AND bd.pkOrgBuilding =:pkOrgBuilding		");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("pkRoomType", pkRoomType)
				.setParameter("pkOrgBuilding", pkOrgBuilding).list();
		List<RoomView> returnList = new ArrayList<RoomView>();
		if(!objList.isEmpty()){
			for(int i = 0; i < objList.size(); i++){
				Long pkRoomRegister = objList.get(i)[0] == null ? null : Long.valueOf(objList.get(i)[0].toString());
				Long pkRoom = objList.get(i)[1] == null ? null : Long.valueOf(objList.get(i)[1].toString());
				String code = objList.get(i)[2] == null ? null : objList.get(i)[2].toString();
				RoomView rv = new RoomView();
				rv.setPkRoomRegister(pkRoomRegister);
				rv.setPkRoom(pkRoom);
				rv.setCode(code);
				rv.setRoomStatus(RoomStatus.Free);
				returnList.add(rv);
			}
		}
		
		return returnList;
	}
}
